const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require('../../models/db')
// 采购汇总-商品
router.post('/sale/detail', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let billNo = formData.billNo?formData.billNo:'';
    let buId = formData.buId?formData.buId:0;
    let deliverId = formData.deliverId?formData.deliverId:0;
    let brandId = formData.brandId?formData.brandId:0;
    let salesId = formData.salesId?formData.salesId:0;
    let send = formData.send?formData.send:[];
    let transType = formData.transType?formData.transType:0;
    let goodCatCode = await tool.getGoodCode(ctx)
    let invName = formData.invName?formData.invName:'';
    let goodName = formData.goodName?formData.goodName:'';
    let subRemark = formData.subRemark?formData.subRemark:'';
    let contactCatId = formData.contactCatId?formData.contactCatId:0;
    let currentPage = body.currentPage?body.currentPage:1;
    let pageSize = body.pageSize?body.pageSize:200;
    let offset = (currentPage-1) * pageSize;

    let excel = body.excel
    let sqls = `select a.id, a.tenantId, billDate, a.billNo, billState, billType, a.transType,a.contactCatName,a.address,a.linkMobile,linkName,
                       billStateName,printNo,a.salesName,a.deliverNames,b.barCode1,b.brandId,b.brandName,
                       transTypeName, buId, contactCatId, buName, totalAmount, disAmount, ysAmount, rpAmount, accId,
                       accAmount,b.send,b.deduction,
                       accId1, accAmount1,
                       b.remark,billStateSort,goodCatName,invName ,spec,unit,price ,b.remark subRemark ,
                       b.qtyReal qty,
                       b.amountReal amount,b.unit1,b.unit2,b.calNum*b.qtyReal qty2 ,b.assQty ,
                       (b.calNum*b.qtyReal)/b.spec qty3,
                       b.sendPrice,iif(isnull(b.send,'')<>'',b.sendPrice*((b.calNum*b.qty)/b.spec),0) sendAmount ,
                       a.hxAt,a.hxAmount,a.hxBillNo , b.invId 
                from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid 
                where a.tenantId=${tenantId} and a.billType='SALE' and billState='accounted'
    `
    if(billNo) {
        sqls += ` and a.billNo like '%${billNo}%' `
    }
    if(goodName) {
        sqls += ` and b.invName like '%${goodName}%'  `
    }
    if(send.length > 0) {
        let s = []
        send.map(row => {
            s.push(`'${row}'`)
        })
        sqls += ` and b.send in(${s.join(',')}) `
    }
    if(transType) {
        sqls+= ` and a.transType=${transType} `
    }
    if(brandId) {
        sqls += ` and b.brandId=${brandId} `
    }
    if(contactCatId) {
        sqls += `  and a.contactCatId=${contactCatId} `
    }
    if(salesId) {
        sqls += ` and a.salesId=${salesId} `
    }
    if(deliverId) {
        let admin = await db.models['admin'].findOne({
            where: {
                tenantId,
                id: deliverId
            }
        })
        if(admin) {
            sqls += ` and a.deliverNames like '%${admin.name}%' `
        }
    }
    if(beginDate) {
        sqls += ` and a.billDate>='${beginDate}' `
    }
    if(beginDate) {
        sqls += ` and a.billDate<='${endDate}' `
    }
    if(buId) {
        sqls += ` and a.buId=${buId} `
    }
    if(invName) {
        sqls += ` and b.invName like '%${invName}%' `
    }
    if(subRemark) {
        sqls += ` and b.remark like '%${subRemark}%' `
    }
    if(goodCatCode) {
        sqls += ` and b.goodCatCode like '${goodCatCode}%' `
    }
    if(excel) {
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    }
    else {
        let sort = tool.getSort(ctx)
        let sorts = ' billStateSort, billDate desc '
        if(sort) sorts = sort
        let totalModel = await db.query(`select count(*) total from (${sqls}) a `, {type: QueryTypes.SELECT, plain: true})
        sqls = ` select * from (${sqls}) a order by ${sorts} offset ${offset} rows fetch next ${pageSize} rows only;`
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: totalModel?totalModel.total: 0,
            list: list
        })
    }
})





module.exports = router;